﻿//----------------------------------------------------
//    版权：
//    作者：
//建立时间：2012-09-11 00:00:33
//    描述：
//----------------------------------------------------
using System;
using System.Data;
using System.Text;
using System.Linq;
using System.Collections.Generic;


using WinnerCMS.Common;
using WinnerCMS.Model;
using System.Data.Entity;

namespace WinnerCMS.Data
{
    /// <summary>
    /// 数据访问层CMS_Field。
    /// </summary>
    public class FieldDal
    {
        #region  添加数据
        /// <summary>
        /// 添加数据
        /// </summary>
        public void Add(Field model)
        {
            using (Entities db = new Entities())
            {
                int? iOrderId = (from u in db.Fields
                                 select u.OrderId).Max();
                if (iOrderId == null)
                {
                    model.OrderId = 0;
                }
                else
                {
                    model.OrderId = (int)iOrderId + 1;
                }

                db.Fields.Add(model);
                db.SaveChangesAsync().Wait();
            }
        }
        #endregion

        #region  修改数据
        /// <summary>
        /// 修改
        /// </summary>
        public void Edit(Field model)
        {
            using (Entities db = new Entities())
            {
                db.Fields.Attach(model);
                db.Entry(model).State = EntityState.Modified;
                db.SaveChangesAsync().Wait();
            }
        }
        #endregion

        #region  删除数据
        /// <summary>
        /// 删除
        /// </summary>
        public void Delete(int Id)
        {
            using (Entities db = new Entities())
            {
                int iOrderId = (from u in db.Fields
                                where u.Id == Id
                                select u.OrderId).First();

                Field model = db.Fields.Where(x => x.Id == Id).FirstOrDefault();
                db.Fields.Remove(model);
                db.SaveChangesAsync().Wait();

                var q = db.Fields.Where(x => x.OrderId > iOrderId);

                foreach (var v in q)
                {
                    v.OrderId = iOrderId - 1;
                }
                db.SaveChangesAsync().Wait();
            }
        }

        #endregion

        #region  加载数据
        /// <summary>
        /// 加载数据
        /// </summary>
        /// <param name="Id"></param>
        /// <returns></returns>
        public Field GetInfo(int Id)
        {
            using (Entities db = new Entities())
            {
                return db.Fields.Find(Id);
            }
        }
        #endregion

        #region 替换
        /// <summary>
        /// 替换
        /// </summary>
        /// <param name="modelId"></param>
        /// <param name="categoryId"></param>
        /// <param name="fields"></param>
        /// <param name="oldString"></param>
        /// <param name="newString"></param>
        /// <param name="replaceType"></param>
        public void RepalceString(byte modelId, string categoryId, string fields, string oldString, string newString, int replaceType)
        {
            var list = new WinnerCMS.Data.ModelDal().GetList().Values.ToList();
            string tableName = list.Find(m => m.Id == modelId).DataTable;

            MyStringBuilder Sql = new MyStringBuilder();
            Sql.Append("UPDATE ");
            Sql.Append(tableName);
            Sql.Append(" SET ");
            foreach (string Field in fields.Split(','))
            {
                Sql.AppendFormat(" [{0}]=REPLACE([{0}],'{1}','{2}'),", Field, oldString, newString);
            }
            Sql.TrimEnd();

            if (categoryId != "0")
            {
                Sql.Append(" WHERE CategoryId=" + categoryId);
            }

            SQLBaseV2.ExecuteNonQuery(Sql.ToString());

        }
        #endregion

        public void AddField(string Sql)
        {
            SQLBaseV2.ExecuteNonQuery(Sql);
        }
        /// <summary>
        /// 删除表字段
        /// </summary>
        /// <param name="tableName"></param>
        /// <param name="fieldName"></param>
        public void DeleteField(string tableName, string fieldName)
        {
            string sql = string.Format("ALTER TABLE {0} DROP COLUMN {1}", tableName, fieldName);
            SQLBaseV2.ExecuteNonQuery(sql);
        }

        #region 该模型的扩展字段
        /// <summary>
        /// 该模型的扩展字段
        /// </summary>
        /// <param name="modelId"></param>
        /// <param name="isShowInMember"></param>
        /// <returns></returns>
        public IList<Field> FiedList(byte modelId, bool isShowInMember)
        {
            using (Entities db = new Entities())
            {
                var q = from x in db.Fields where x.ModelId == modelId && !x.IsStop select x;
                if (isShowInMember)
                {
                    q = q.Where(x => x.IsShowInMember);
                }
                q = q.OrderByDescending(x => x.OrderId).ThenByDescending(x => x.Id);
                return q.ToArray();
            }
        }
        #endregion

        #region 加载扩展字段数据
        /// <summary>
        /// 加载扩展字段数据
        /// </summary>
        /// <param name="ModelId"></param>
        /// <param name="IsShowInMember"></param>
        /// <param name="TableName"></param>
        /// <param name="ContentDataKey"></param>
        /// <returns></returns>
        public DataRow ExtendFieldData(byte ModelId, bool IsShowInMember, string TableName, int ContentDataKey)
        {
            var list = FiedList(ModelId, IsShowInMember);

            if (list.Count == 0) return null;//如果没有扩展字段就返回ｎｕｌｌ

            MyStringBuilder FieldList = new MyStringBuilder();

            foreach (Field model in list)
            {
                FieldList.Append(model.FieldName);
                FieldList.Append(",");
            }
            FieldList.TrimEnd();

            using (Entities db = new Entities())
            {
                return db.Database.SqlQuery<DataRow>("SELECT " + FieldList + " FROM " + TableName + " WHERE Id=" + ContentDataKey).FirstOrDefault();
            }
        }
        /// <summary>
        /// 加载扩展字段数据
        /// </summary>
        /// <param name="ModelId"></param>
        /// <param name="GoodsID"></param>
        /// <param name="IsShowInMember"></param>
        /// <param name="TableName"></param>
        /// <param name="ContentDataKey"></param>
        /// <returns></returns>
        public DataRow ExtendFieldData(byte ModelId, int GoodsID, bool IsShowInMember, string TableName, int ContentDataKey)
        {
            return SQLBaseV2.ReturnDataRow(@"declare @s varchar(8000)
set @s='SELECT TOP 1 GoodsID'
select @s=@s+',['+FieldName+']='''+FieldValue+''''
FROM CMS_GoodsCustomFieldValue Where GoodsID=" + GoodsID + @"
SET @s=@s+' FROM CMS_GoodsCustomFieldValue'
exec(@s)");
        }
        #endregion

        #region 取得商城模型的扩展字段
        /// <summary>
        /// 取得商城模型的扩展字段
        /// </summary>
        /// <param name="ModelId"></param> 
        /// <returns></returns>
        public IList<Field> GetShopModelField(byte ModelId)
        {
            using (Entities db = new Entities())
            {
                var query = from x in db.Fields
                            where db.Models.Any(y => x.ModelId == y.Id)
                            orderby x.OrderId descending, x.Id descending
                            select x;


                return query.ToArray().Select(x => new Field()
                {
                    Id = x.Id,
                    FieldName = x.FieldName, 
                    Title = x.Title + " (" + x.FieldName + ")",
                    Tips = x.Tips,
                    FieldType = x.FieldType,
                    DefaultValue = x.DefaultValue,
                    ControlType = x.ControlType,
                    Indispensable = x.Indispensable,
                    Width = x.Width,
                    MaxFieldLength = x.MaxFieldLength
                }).ToArray();
            }
        }
        #endregion

        #region 取得商城模型的栏目的扩展字段
        /// <summary>
        /// 取得商城模型的栏目的扩展字段
        /// </summary>
        /// <param name="ModelId"></param>
        /// <param name="CategoryId"></param>
        /// <returns></returns>
        public IList<Field> GetShopModelField(byte ModelId, int CategoryId)
        {
            if (CategoryId == 0)
                return new List<Field>();

            using (Entities db = new Entities())
            {
                string sql = "SELECT * FROM CMS_Field WHERE ModelId={0} AND IsStop=0 And FieldName in (SELECT FieldName FROM CMS_GoodsCustomField WHERE CategoryId={1}) ORDER BY OrderId DESC,Id DESC";

                return db.Fields.SqlQuery(sql, ModelId, CategoryId).ToArray();
            }
        }
        #endregion

        #region 绑定分页数据
        /// <summary>
        /// 绑定分页数据
        /// </summary>
        /// <param name="ModelId"></param>
        /// <param name="pp"></param>
        /// <returns></returns>
        public IList<Field> BindList(byte ModelId, PagingParam pp)
        {
            using (Entities db = new Entities())
            {
                var query = from x in db.Fields
                            where x.ModelId == ModelId
                            select x;
                pp.RecordCount = query.Count();

                return query.OrderByDescending(x => x.Id).Skip(pp.Skip).Take(pp.PageSize).ToList();
            }
        }
        #endregion
    }

}